IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Merchants_Members]') AND parent_object_id = OBJECT_ID(N'[dbo].[Merchants]'))
ALTER TABLE [dbo].[Merchants] DROP CONSTRAINT [FK_Merchants_Members]
GO
/****** Object:  Table [dbo].[Merchants]    Script Date: 04/22/2010 16:11:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Merchants]') AND type in (N'U'))
DROP TABLE [dbo].[Merchants]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Members_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[Members]'))
ALTER TABLE [dbo].[Members] DROP CONSTRAINT [FK_Members_Users]
GO
/****** Object:  Table [dbo].[Members]    Script Date: 04/22/2010 16:10:55 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Members]') AND type in (N'U'))
DROP TABLE [dbo].[Members]
GO

/****** Object:  Table [dbo].[Members]    Script Date: 04/22/2010 16:10:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Members](
	[MemberID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NOT NULL,
	[UserName] [nvarchar](50) NULL,
	[JoinDate] [datetime] NOT NULL,
	[FirstName] [varchar](40) NULL,
	[LastName] [varchar](40) NULL,
	[Company] [varchar](50) NULL,
	[BillCountry] [varchar](30) NULL,
	[BillIntlRegion] [varchar](50) NULL,
	[BillStateProv] [varchar](30) NULL,
	[BillCity] [varchar](30) NULL,
	[BillZipPostal] [varchar](10) NULL,
	[BillStreet1] [varchar](50) NULL,
	[BillStreet2] [varchar](50) NULL,
	[ShipCountry] [varchar](30) NULL,
	[ShipIntlRegion] [varchar](50) NULL,
	[ShipStateProv] [varchar](30) NULL,
	[ShipCity] [varchar](30) NULL,
	[ShipZipPostal] [varchar](10) NULL,
	[ShipStreet1] [varchar](50) NULL,
	[ShipStreet2] [varchar](50) NULL,
	[HomePhone] [nvarchar](20) NULL,
	[WorkPhone] [nvarchar](20) NULL,
	[MobilePhone] [nvarchar](20) NULL,
	[Pager] [nvarchar](20) NULL,
	[Fax] [nvarchar](20) NULL,
	[Email] [nvarchar](50) NULL,
	[DOB] [date] NULL,
	[SINid] [int] NULL,
	[Gender] [varchar](6) NULL,
	[Subscribed] [bit] NULL,
 CONSTRAINT [PK_Members] PRIMARY KEY CLUSTERED 
(
	[MemberID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Members]  WITH CHECK ADD  CONSTRAINT [FK_Members_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[Members] CHECK CONSTRAINT [FK_Members_Users]
GO

/****** Object:  Table [dbo].[Merchants]    Script Date: 04/22/2010 16:11:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Merchants](
	[MerchantID] [int] IDENTITY(1,1) NOT NULL,
	[MemberID] [int] NOT NULL,
	[AccountManagerID] [int] NULL,
	[BusinessName] [varchar](50) NULL,
	[LegalCorporateName] [varchar](50) NULL,
	[ContactPerson] [varchar](60) NULL,
	[ContactTitle] [varchar](50) NULL,
	[BusinessPhone] [nvarchar](25) NULL,
	[WebSite] [nvarchar](255) NULL,
	[CallFlag] [bit] NOT NULL,
	[Expense1] [varchar](50) NULL,
	[Expense2] [varchar](50) NULL,
	[Expense3] [varchar](50) NULL,
	[Expense4] [varchar](50) NULL,
	[Expense5] [varchar](50) NULL,
 CONSTRAINT [PK_Merchants] PRIMARY KEY CLUSTERED 
(
	[MerchantID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Merchants]  WITH CHECK ADD  CONSTRAINT [FK_Merchants_Members] FOREIGN KEY([MemberID])
REFERENCES [dbo].[Members] ([MemberID])
GO
ALTER TABLE [dbo].[Merchants] CHECK CONSTRAINT [FK_Merchants_Members]
GO


IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_CardOrders_Users]') AND parent_object_id = OBJECT_ID(N'[dbo].[CardOrders]'))
ALTER TABLE [dbo].[CardOrders] DROP CONSTRAINT [FK_CardOrders_Users]
GO
/****** Object:  Table [dbo].[CardOrders]    Script Date: 04/23/2010 09:38:27 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CardOrders]') AND type in (N'U'))
DROP TABLE [dbo].[CardOrders]
GO
/****** Object:  Table [dbo].[CardOrders]    Script Date: 04/23/2010 09:38:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CardOrders](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[UserID] [int] NULL,
	[FirstName] [nvarchar](25) NULL,
	[LastName] [nvarchar](25) NULL,
	[Address] [nvarchar](100) NULL,
	[City] [nvarchar](30) NULL,
	[StateProv] [nvarchar](40) NULL,
	[Country] [nvarchar](50) NULL,
	[ZipPostal] [nvarchar](10) NULL,
	[ValidFrom] [datetime] NULL,
	[Expiry] [datetime] NULL,
	[IsPrinted] [nvarchar](3) NULL,
 CONSTRAINT [PK_CardOrders] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[CardOrders]  WITH CHECK ADD  CONSTRAINT [FK_CardOrders_Users] FOREIGN KEY([UserID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[CardOrders] CHECK CONSTRAINT [FK_CardOrders_Users]
GO


INSERT INTO [owu-dnn-clb].[dbo].[Members]
           ([UserID]
           ,[UserName]
           ,[JoinDate]
           ,[FirstName]
           ,[LastName]
           ,[Company]
           ,[BillCountry]
           ,[BillIntlRegion]
           ,[BillStateProv]
           ,[BillCity]
           ,[BillZipPostal]
           ,[BillStreet1]
           ,[BillStreet2]
           ,[ShipCountry]
           ,[ShipIntlRegion]
           ,[ShipStateProv]
           ,[ShipCity]
           ,[ShipZipPostal]
           ,[ShipStreet1]
           ,[ShipStreet2]
           ,[HomePhone]
           ,[WorkPhone]
           ,[MobilePhone]
           ,[Pager]
           ,[Fax]
           ,[Email]
           ,[DOB]
           ,[SINid]
           ,[Gender]
           ,[Subscribed])
     VALUES
           (3
           ,'user00'
           ,GETDATE()
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,GETDATE()
           ,1
           ,1
           ,1)
INSERT INTO [owu-dnn-clb].[dbo].[Members]
           ([UserID]
           ,[UserName]
           ,[JoinDate]
           ,[FirstName]
           ,[LastName]
           ,[Company]
           ,[BillCountry]
           ,[BillIntlRegion]
           ,[BillStateProv]
           ,[BillCity]
           ,[BillZipPostal]
           ,[BillStreet1]
           ,[BillStreet2]
           ,[ShipCountry]
           ,[ShipIntlRegion]
           ,[ShipStateProv]
           ,[ShipCity]
           ,[ShipZipPostal]
           ,[ShipStreet1]
           ,[ShipStreet2]
           ,[HomePhone]
           ,[WorkPhone]
           ,[MobilePhone]
           ,[Pager]
           ,[Fax]
           ,[Email]
           ,[DOB]
           ,[SINid]
           ,[Gender]
           ,[Subscribed])
     VALUES
           (4
           ,'user01'
           ,GETDATE()
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,GETDATE()
           ,1
           ,1
           ,1)
INSERT INTO [owu-dnn-clb].[dbo].[Members]
           ([UserID]
           ,[UserName]
           ,[JoinDate]
           ,[FirstName]
           ,[LastName]
           ,[Company]
           ,[BillCountry]
           ,[BillIntlRegion]
           ,[BillStateProv]
           ,[BillCity]
           ,[BillZipPostal]
           ,[BillStreet1]
           ,[BillStreet2]
           ,[ShipCountry]
           ,[ShipIntlRegion]
           ,[ShipStateProv]
           ,[ShipCity]
           ,[ShipZipPostal]
           ,[ShipStreet1]
           ,[ShipStreet2]
           ,[HomePhone]
           ,[WorkPhone]
           ,[MobilePhone]
           ,[Pager]
           ,[Fax]
           ,[Email]
           ,[DOB]
           ,[SINid]
           ,[Gender]
           ,[Subscribed])
     VALUES
           (5
           ,'user02'
           ,GETDATE()
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,GETDATE()
           ,1
           ,1
           ,1)
INSERT INTO [owu-dnn-clb].[dbo].[Members]
           ([UserID]
           ,[UserName]
           ,[JoinDate]
           ,[FirstName]
           ,[LastName]
           ,[Company]
           ,[BillCountry]
           ,[BillIntlRegion]
           ,[BillStateProv]
           ,[BillCity]
           ,[BillZipPostal]
           ,[BillStreet1]
           ,[BillStreet2]
           ,[ShipCountry]
           ,[ShipIntlRegion]
           ,[ShipStateProv]
           ,[ShipCity]
           ,[ShipZipPostal]
           ,[ShipStreet1]
           ,[ShipStreet2]
           ,[HomePhone]
           ,[WorkPhone]
           ,[MobilePhone]
           ,[Pager]
           ,[Fax]
           ,[Email]
           ,[DOB]
           ,[SINid]
           ,[Gender]
           ,[Subscribed])
     VALUES
           (6
           ,'user03'
           ,GETDATE()
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,GETDATE()
           ,1
           ,1
           ,1)
INSERT INTO [owu-dnn-clb].[dbo].[Members]
           ([UserID]
           ,[UserName]
           ,[JoinDate]
           ,[FirstName]
           ,[LastName]
           ,[Company]
           ,[BillCountry]
           ,[BillIntlRegion]
           ,[BillStateProv]
           ,[BillCity]
           ,[BillZipPostal]
           ,[BillStreet1]
           ,[BillStreet2]
           ,[ShipCountry]
           ,[ShipIntlRegion]
           ,[ShipStateProv]
           ,[ShipCity]
           ,[ShipZipPostal]
           ,[ShipStreet1]
           ,[ShipStreet2]
           ,[HomePhone]
           ,[WorkPhone]
           ,[MobilePhone]
           ,[Pager]
           ,[Fax]
           ,[Email]
           ,[DOB]
           ,[SINid]
           ,[Gender]
           ,[Subscribed])
     VALUES
           (7
           ,'user04'
           ,GETDATE()
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,GETDATE()
           ,1
           ,1
           ,1)
           
           
INSERT INTO [owu-dnn-clb].[dbo].[Merchants]
           ([MemberID]
           ,[AccountManagerID]
           ,[BusinessName]
           ,[LegalCorporateName]
           ,[ContactPerson]
           ,[ContactTitle]
           ,[BusinessPhone]
           ,[WebSite]
           ,[CallFlag]
           ,[Expense1]
           ,[Expense2]
           ,[Expense3]
           ,[Expense4]
           ,[Expense5])
     VALUES
           (1
           ,1
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,1
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00'
           ,'user00')
INSERT INTO [owu-dnn-clb].[dbo].[Merchants]
           ([MemberID]
           ,[AccountManagerID]
           ,[BusinessName]
           ,[LegalCorporateName]
           ,[ContactPerson]
           ,[ContactTitle]
           ,[BusinessPhone]
           ,[WebSite]
           ,[CallFlag]
           ,[Expense1]
           ,[Expense2]
           ,[Expense3]
           ,[Expense4]
           ,[Expense5])
     VALUES
           (2
           ,1
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,1
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01'
           ,'user01')
INSERT INTO [owu-dnn-clb].[dbo].[Merchants]
           ([MemberID]
           ,[AccountManagerID]
           ,[BusinessName]
           ,[LegalCorporateName]
           ,[ContactPerson]
           ,[ContactTitle]
           ,[BusinessPhone]
           ,[WebSite]
           ,[CallFlag]
           ,[Expense1]
           ,[Expense2]
           ,[Expense3]
           ,[Expense4]
           ,[Expense5])
     VALUES
           (3
           ,1
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,1
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02'
           ,'user02')
INSERT INTO [owu-dnn-clb].[dbo].[Merchants]
           ([MemberID]
           ,[AccountManagerID]
           ,[BusinessName]
           ,[LegalCorporateName]
           ,[ContactPerson]
           ,[ContactTitle]
           ,[BusinessPhone]
           ,[WebSite]
           ,[CallFlag]
           ,[Expense1]
           ,[Expense2]
           ,[Expense3]
           ,[Expense4]
           ,[Expense5])
     VALUES
           (4
           ,1
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,1
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03'
           ,'user03')
INSERT INTO [owu-dnn-clb].[dbo].[Merchants]
           ([MemberID]
           ,[AccountManagerID]
           ,[BusinessName]
           ,[LegalCorporateName]
           ,[ContactPerson]
           ,[ContactTitle]
           ,[BusinessPhone]
           ,[WebSite]
           ,[CallFlag]
           ,[Expense1]
           ,[Expense2]
           ,[Expense3]
           ,[Expense4]
           ,[Expense5])
     VALUES
           (5
           ,1
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,1
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04'
           ,'user04')
           
--blow away the Card Ordered user roles form the simulated users
DELETE UR 
FROM 
	[dbo].[Users] U
	INNER JOIN [dbo].[UserRoles] UR
		ON U.UserID=UR.UserID
	INNER JOIN [dbo].[Roles] R
		ON UR.RoleID=R.RoleID
		AND (
				   R.RoleName='Card Ordered'
				OR R.RoleName='Card Activated'
			)
WHERE U.Username like '%user%'